package com.sport.common.excel;

import com.jeesite.common.codec.EncodeUtils;
import com.sport.common.callbcak.MethodCallback;
import com.sport.common.collect.ListUtils;
import com.sport.model.excel.annotation.ExcelFields;
import com.sport.model.excel.fieldtype.FieldType;
import com.sport.common.lang.DateUtils;
import com.sport.common.lang.StringUtils;



import com.sport.model.excel.annotation.ExcelField;
import com.sport.common.lang.ObjectUtils;
import com.sport.common.reflect.ReflectUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * 实现Closeable接口,可以关闭释放所持有的任何资源
 */
public class ExcelImport implements Closeable{
        //日志记录器声明
        private static Logger log= LoggerFactory.getLogger(ExcelImport.class);


        /**
         * 工作簿对象,这个Excel文件
         */
        private Workbook wb;

        /**
         * 工作表对象,Excel中的一个工作表
         */
        private Sheet sheet;

        /**
         * 标题行数,表示Excel文件中标题所在的行号
         */
        private int headerNum;

        /**
         * 存储字段类型临时数据
         *   添加键值对到fieldTypes
         *         StringFieldType stringFieldType=new StringFieldType("String");
         *         fieldType.put(stringFieldType.getClass(),stringFieldType);
         *         NumberFieldType numberFieldType = new NumberFieldType("Number");
         */
        private Map<Class<? extends FieldType>,FieldType> fieldTypeMap= new HashMap<>();

        //存储字典工具类的类型
        @SuppressWarnings("rawtypes")
        private static Class dictUtilsClass=null;

        /**
         * 构造函数
         * @param fileName 导入文件对象
         * @param is
         * @param headerNum  标题行数,数据行号=标题行数+1
         * @param sheetIndexOrName  工作表编号或名称
         * @throws InvalidFormatException
         * @throws IOException
         */
        public ExcelImport(String fileName, InputStream is,int headerNum,Object sheetIndexOrName)throws InvalidFormatException, IOException {
                if(StringUtils.isBlank(fileName)){
                        throw new ExcelException("导入文档为空!");
                }else if(fileName.toLowerCase().endsWith("xls")){
                        this.wb=new HSSFWorkbook(is);
                }else if(fileName.toLowerCase().endsWith("xlsx")){
                        this.wb=new XSSFWorkbook(is);
                }else{
                        throw new ExcelException("文档格式不正确!");
                }
                this.setSheet(sheetIndexOrName,headerNum);
                log.debug("Initialize success.");
        }

        private void setSheet(Object sheetIndexOrName, int headerNum) {
                //判断sheetIndexOrName是否为Integer或Long
                if(sheetIndexOrName instanceof Integer||sheetIndexOrName instanceof Long){
                        this.sheet=this.wb.getSheetAt(ObjectUtils.toInteger(sheetIndexOrName));
                }else {
                        this.sheet=this.wb.getSheet(ObjectUtils.toString(sheetIndexOrName));
                }
                if(this.sheet==null){
                        throw new ExcelException("没有找到'"+sheetIndexOrName+"'工作表!");
                }
                this.headerNum=headerNum;
        }

        /**
         * 构造函数
         * @param file
         * @param headerNum
         * @param sheetIndexOrName
         * @throws InvalidFormatException
         * @throws IOException
         */
        public ExcelImport(File file, int headerNum, Object sheetIndexOrName)
                throws InvalidFormatException, IOException {
                this(file.getName(), new FileInputStream(file), headerNum, sheetIndexOrName);
        }

        /**
         * 构造函数
         * @param multipartFile
         * @param headerNum
         * @param sheetIndexName
         * @throws InvalidFormatException
         * @throws IOException
         */
        public ExcelImport(MultipartFile multipartFile,int headerNum,Object sheetIndexName)
        throws InvalidFormatException ,IOException{
                this(multipartFile.getOriginalFilename(),multipartFile.getInputStream(),headerNum,sheetIndexName);

        }
        /**
         * 构造函数
         * @param file
         * @throws InvalidFormatException
         * @throws IOException
         */
        public ExcelImport(File file) throws InvalidFormatException,IOException {
                this(file,0,0);
        }

        /**
         * 添加到注解集合中
         * @param annotationList
         * @param ef
         * @param form
         * @param type
         * @param groups
         */
        private void addAnnotation(List<Object[]> annotationList, ExcelField ef, Object form, ExcelField.Type type, String... groups){
                //若字段不为空且类型值确定
                if(ef!=null&&(ef.type()== ExcelField.Type.ALL || ef.type()==type)){
                        //若已经存在归属组
                        if(groups!=null && groups.length>0){
                                boolean inGroup=false;
                                //遍历归属组里的字段
                                for (String g : groups) {
                                        if(inGroup){
                                                break;
                                        }
                                        //遍历本字段的归属组
                                        for (String efg : ef.groups()) {
                                                //如果找到一个g在ef的归属组,则找到了匹配的组
                                                if(StringUtils.equals(g,efg)){
                                                        inGroup=true;
                                                        //添加到注解中
                                                        annotationList.add(new Object[]{ef,form});
                                                        break;
                                                }
                                        }

                                }

                        }
                        //否则,直接加到注解列表里
                        else{
                                annotationList.add(new Object[]{ef,form});
                        }
                }
        }

        /**
         * 获取当前工作簿
         *
         */
        public Workbook getWorkbook(){
                return wb;
        }

        /**
         * 获取行对象
         */
        public Row getRow(int rownum){
                //获取行对象
                Row row=this.sheet.getRow(rownum);
                if(row==null){
                        return null;
                }
                //验证是否是空行,是返回null
                short cellNum=0;
                short emptyNum=0;
                //单元格迭代器
                Iterator<Cell> it = row.cellIterator();
                while (it.hasNext()){
                        cellNum++;
                        //单元格
                        Cell cell = it.next();
                        if(StringUtils.isBlank(cell.toString())){
                                emptyNum++;
                        }
                }
                if(cellNum==emptyNum){
                        return  null;
                }
                return row;
        }
        /**
         * 获取数据行号
         */
        public int getDataRowNum(){
                return headerNum;
        }

        /**
         * 获取最后一个数据行号
         */
        public int getLastDataRowNum(){
                return this.sheet.getLastRowNum()+1;
        }

        /**
         * 获取最后一个列号
         */
        public int getLastCellNum(){
                Row row = this.getRow(headerNum);
                return  row ==null ? 0 :row.getLastCellNum();
        }

        /**
         * 获取单元格值
         */
        public Object getCellValue(Row row, int column) {
                if (row == null) {
                        return row;
                }
                Object val = "";
                try {
                        //获取单元格
                        Cell cell = row.getCell(column);
                        if (cell != null) {
                                //判断是否为数字
                                if (cell.getCellType() == CellType.NUMERIC) {
                                        val = cell.getNumericCellValue();
                                        //判断是否为日期
                                        if (DateUtil.isCellDateFormatted(cell)) {
                                                val = DateUtil.getJavaDate((Double) val); //POI Excel 日期格式转换
                                        } else {
                                                //判断是否为小数
                                                if ((Double) val % 1 > 0) {
                                                        val = new DecimalFormat("0.00").format(val);
                                                } else {
                                                        //整形
                                                        val = new DecimalFormat("0").format(val);
                                                }
                                        }
                                }
                                //判断是否为字符串类型
                                else if (cell.getCellType() == CellType.STRING) {
                                        val = cell.getStringCellValue();
                                }
                                //判断是否为公式类型
                                else if (cell.getCellType() == CellType.FORMULA) {
                                        try {
                                                //尝试获得计算结果
                                                val = cell.getStringCellValue();
                                        } catch (Exception e) {
                                                //创建一个求值器对象,从单元格所在工作表和工作簿获取
                                                FormulaEvaluator evaluator
                                                        = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                                                //对单元格进行求值
                                                evaluator.evaluateFormulaCell(cell);
                                                //获取求值后的结果
                                                CellValue cellValue = evaluator.evaluate(cell);
                                                //对求值后的结果类型进行处理
                                                switch (cellValue.getCellType()) {
                                                        case NUMERIC:
                                                                val = cellValue.getNumberValue();
                                                                break;
                                                        case STRING:
                                                                val = cellValue.getStringValue();
                                                                break;
                                                        case BOOLEAN:
                                                                val = cellValue.getBooleanValue();
                                                                break;
                                                        case ERROR:
                                                                val = cellValue.getErrorValue();
                                                                break;
                                                        default:
                                                                //否则将公式字符串赋值给val
                                                                val = cell.getCellFormula();
                                                }
                                        }
                                } else if (cell.getCellType() == CellType.BOOLEAN) {
                                        val = cell.getBooleanCellValue();
                                } else if (cell.getCellType() == CellType.ERROR) {
                                        val = cell.getErrorCellValue();
                                }
                        }
                }catch (Exception e){
                        return val;
                }

                return val;
        }

        /**
         * 获取导入数据列表
         * @param cls
         * @param groups
         * @return
         * @param <E>
         * @throws Exception
         */
        public <E> List<E> getDataList(Class<E> cls,String... groups)throws Exception{
                return getDataList(cls,false,groups);
        }


        public <E> List<E> getDataList(Class<E> cls,boolean isThrowException, String... groups)throws Exception{
                return getDataList(cls,params ->{
                    if (isThrowException){
                            Exception ex=(Exception) params[0];
                            int rowNum=(int)params[1];
                            int columNum = (int)params[2];
                            //抛出异常信息包含出错的单元格坐标
                            throw new ExcelException("get cell value ["+rowNum+","+columNum+"]",ex);
                    }
                    return  null;
                },groups);
        }

        public Map<String,String> getTitle(ExcelImport ei){
                Map<String,String> titleMap=new HashMap<>();
                int titleRowCount=2;
                for(int j=0;j<titleRowCount;j++){
                        Row titleRow = ei.sheet.getRow(j);
                        String titleKey=null;
                        String titleValue=null;
                        //获取标题总列数
                        int columnCount = titleRow.getLastCellNum();
                        for(int i = 0;i < columnCount;i++){
                                Cell cell = titleRow.getCell(i);
                                if (cell == null) continue;
                                if (i==0) {
                                        titleKey = cell.getStringCellValue();
                                } else{
                                        titleValue= cell.getStringCellValue();
                                        if(titleValue!=null&&titleValue!="") titleMap.put(titleKey,titleValue);
                                }
                        }

                }
                return titleMap;
        }

        /**
         * 获取导入数据列表
         * @param cls 导入对象类型
         * @param exceptionCallback 遇到异常时回调方法
         * @param groups 导入分组
         * @return
         * @param <E>
         * @throws Exception
         */
        public <E> List<E> getDataList(Class<E> cls, MethodCallback exceptionCallback, String... groups)throws Exception{
                List<Object[]> annotationList = ListUtils.newArrayList();
                //获得构造器注解
                Constructor<?>[] constructors = cls.getConstructors();
                for (Constructor<?> cs : constructors) {
                        //获得构造器上的注解
                        ExcelFields efs = cs.getAnnotation(ExcelFields.class);
                        if(efs!=null && efs.value()!=null){
                                for (ExcelField ef : efs.value()) {
                                        //将注解添加到annotationList中
                                        addAnnotation(annotationList,ef,cs, ExcelField.Type.IMPORT,groups);
                                }

                        }
                }
                //获得属性上注解
                Field[] fs = cls.getDeclaredFields();
                for (Field f : fs) {
                        ExcelFields efs = f.getAnnotation(ExcelFields.class);
                        if(efs!=null&& efs.value()!=null){
                                for (ExcelField ef : efs.value()) {
                                        addAnnotation(annotationList,ef,f, ExcelField.Type.IMPORT,groups);
                                }

                        }
                        ExcelField ef = f.getAnnotation(ExcelField.class);
                        addAnnotation(annotationList,ef,f, ExcelField.Type.IMPORT,groups);
                        //修改为可访问public
                        ReflectUtils.makeAccessible(f);
                }
                //获得方法上的注解
                Method[] ms = cls.getDeclaredMethods();
                for (Method m : ms) {
                        ExcelFields efs = m.getAnnotation(ExcelFields.class);
                        if(efs!=null&& efs.value()!=null){
                                for (ExcelField ef : efs.value()) {
                                        addAnnotation(annotationList,ef,m, ExcelField.Type.IMPORT,groups);
                                }

                        }
                        ExcelField ef = m.getAnnotation(ExcelField.class);
                        addAnnotation(annotationList,ef,m, ExcelField.Type.IMPORT,groups);
                        ReflectUtils.makeAccessible(m);
                }
                //属性排序
                Collections.sort(annotationList, new Comparator<Object[]>() {
                        @Override
                        public int compare(Object[] o1, Object[] o2) {
                                return Integer.valueOf(((ExcelField)o1[0]).sort()).compareTo(
                                        Integer.valueOf(((ExcelField)o2[0]).sort()));
                        }
                });

                //获得excel数据
                List<E> dataList=ListUtils.newArrayList();
                for (int i=this.getDataRowNum();i<this.getLastDataRowNum();i++){
                        //创建对象e，通过反射创建导入对象的实例
                        E e = cls.getDeclaredConstructor().newInstance();
                        //获取行
                        Row row = this.getRow(i);
                        if(row==null){
                                continue;
                        }
                        StringBuilder stringBuilder = new StringBuilder();
                        for(int j=0;j<annotationList.size();j++){
                                //获取某列的注解
                                Object[] objects = annotationList.get(j);
                                ExcelField ef = (ExcelField) objects[0];
                                int column=(ef.column()!=-1)? ef.column():j;
                                //获取该单元格数据
                                Object cellValue = this.getCellValue(row, column);
                                if(cellValue!=null){
                                        //如果注解属性不为空
                                        if(StringUtils.isNotBlank(ef.dictType())){
                                            try{
                                                if(dictUtilsClass==null){
                                                     dictUtilsClass=Class.forName("com.jeesite.modules.sys.utils.DictUtils");
                                                }
                                                //反射获取字典值
                                                    ReflectUtils.invokeMethodByName(dictUtilsClass,"getDictValues",
                                                            ef.dictType(),cellValue.toString(),"");
                                            }catch (Exception ex){
                                                    log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
                                                    //获取不到设为null
                                                    cellValue = null;
                                            }
                                        }
                                        //获得参数类型进行类型转换
                                        Class<?> valType = Class.class;
                                        //进行类型转换和处理
                                        //若为属性类型
                                        if(objects[1] instanceof Field){
                                                valType=((Field)objects[1]).getType();
                                        }
                                        //若为方法类型
                                        else if(objects[1] instanceof Method){
                                                Method method = (Method) objects[1];
                                                //若方法为get型
                                                if("get".equals(method.getName().substring(0,3))){
                                                        valType=method.getReturnType();
                                                }
                                                //若为set型
                                                else if("set".equals(method.getName().substring(0,3))){
                                                        valType=(((Method) objects[1])).getParameterTypes()[0];
                                                }
                                        }
                                        try{
                                                //如果字段名不为空
                                                if(StringUtils.isNotBlank(ef.attrName())){
                                                        if (ef.fieldType()!= FieldType.class){
                                                                FieldType ft = getFieldType(ef.fieldType());
                                                                cellValue=ft.getValue(ObjectUtils.toString(cellValue));
                                                        }
                                                }else{
                                                        //判断值的类型
                                                        if(cellValue!=null){
                                                                if (valType==String.class){
                                                                     String s=String.valueOf(cellValue.toString());
                                                                     //去掉".0"
                                                                     if(StringUtils.endsWith(s,".0")){
                                                                             cellValue=StringUtils.substringBefore(s,".0");
                                                                     }else{
                                                                             cellValue=String.valueOf(cellValue.toString());
                                                                     }
                                                                }else if(valType==Integer.class){
                                                                        cellValue=Double.valueOf(cellValue.toString()).intValue();
                                                                }else if(valType==Long.class){
                                                                        cellValue=Double.valueOf(cellValue.toString()).longValue();
                                                                }else if(valType==Double.class){
                                                                        cellValue=Double.valueOf(cellValue.toString());
                                                                }else if(valType==Float.class){
                                                                        cellValue=Double.valueOf(cellValue.toString()).floatValue();
                                                                } else if (valType== BigDecimal.class) {
                                                                        cellValue=new BigDecimal(cellValue.toString());
                                                                } else if (valType==Date.class) {
                                                                        if(cellValue instanceof String){
                                                                                cellValue= DateUtils.parseDate(cellValue);
                                                                        } else if (cellValue instanceof Double) {
                                                                                cellValue=DateUtil.getJavaDate((Double)cellValue);//POI Excel日期格式
                                                                        }
                                                                }else{
                                                                        if(ef.fieldType()!= FieldType.class){
                                                                                FieldType ft = getFieldType(ef.fieldType());
                                                                                cellValue=ft.getValue(ObjectUtils.toString(cellValue));
                                                                        }else{
                                                                                //没有指定fieldType,自行根据类型查找相应的转换类(com.sport.common.utils.excel.fieldtype.值的类名+Type)
                                                                                Class<? extends FieldType> fieldType=(Class<? extends FieldType>) Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                                                                        "fieldtype."+valType.getSimpleName()+"Type"));
                                                                                FieldType ft = getFieldType(fieldType);
                                                                                cellValue=ft.getValue(ObjectUtils.toString(cellValue));
                                                                        }
                                                                }
                                                        }
                                                }
                                        }catch (Exception ex){
                                                log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
                                                cellValue = null;
                                                // 参数：Exception ex, int rowNum, int columnNum
                                                exceptionCallback.execute(ex, i, column);
                                        }

                                        //导入的数据进行xss过滤
                                        if(cellValue!=null && cellValue instanceof String){
                                                cellValue= EncodeUtils.xssFilter(cellValue.toString());
                                        }
                                        //设置实体值
                                        if(StringUtils.isNotBlank(ef.attrName())){
                                                ReflectUtils.invokeSetter(e,ef.attrName(),cellValue);
                                        }else{
                                                if(objects[1] instanceof  Field){
                                                        ((Field)objects[1]).set(e,cellValue);
                                                }else if(objects[1] instanceof Method){
                                                        ((Method) objects[1]).invoke(e,cellValue);
                                                }
                                        }
                                }
                                stringBuilder.append(cellValue+",");

                        }
                        dataList.add(e);
                        log.debug("Read success: ["+i+"] "+stringBuilder.toString());

                }
                return dataList;

        }

        public FieldType getFieldType(Class<? extends FieldType> fieldType) throws InstantiationException, IllegalStateException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException, IllegalAccessException {
                FieldType ft = fieldTypeMap.get(fieldType);
                if(ft==null){
                        ft=fieldType.getDeclaredConstructor().newInstance();
                        fieldTypeMap.put(fieldType,ft);
                }
                return ft;
        }
        /**
         *
         * @throws IOException
         */
        @Override
        public void close() throws IOException {
                fieldTypeMap.clear();
                try{
                        wb.close();
                }catch (IOException e){
                        e.printStackTrace();
                }
        }


        /**
         * 导入测试
         */
        public static void main(String[] args) throws Throwable {

                ExcelImport ei = new ExcelImport(new File("C:\\Users\\张泽钦\\Desktop\\myFile.xlsx"));

                for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
                        Row row = ei.getRow(i);
                        if (row == null){
                                continue;
                        }
                        for (int j = 0; j < ei.getLastCellNum(); j++) {
                                Object val = ei.getCellValue(row, j);
                                System.out.print(val+", ");
                        }
                        System.out.println();
                }

        }
}
